package com.example.barry.pdf.PgSQL;

import android.util.Log;
import android.widget.Toast;



import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
 * Created by barry on 16-12-28.
 */

    public class postgresSQLimpl implements postgresSQL {
      //  private static Logger logger = Logger.getLogger(postgresSQLImpl.class);

        private Connection getConnection(String portalHostUrl) {
            Connection connection = null;
            try {
                Class.forName("org.postgresql.Driver");
                String url = "jdbc:postgresql://" + portalHostUrl;
                // logger.info(url);
                connection = DriverManager.getConnection(url, "usr_mesportal", "A8mes!");
                // connection = DriverManager.getConnection(url, "postgres",
                // "root");
            } catch (Exception e) {
                Log.i("DriverException",e.getMessage());
            }
            return connection;
        }

    /**
     * getServerInfo
     *
     * @param portalHost
     *            serverId
     * @return serverInfo
     */
    @SuppressWarnings("finally")
    @Override
    public ServerInfo getServerInfo(String portalHost, int sfdcServerId) {
        ServerInfo sInfo = new ServerInfo();
        String portalHostUrl = portalHost + ":5432/mesportal";
        String sqlString = "select * from mes_server_info where server_id=?";
        Connection connection = getConnection(portalHostUrl);
        try {
            PreparedStatement ps = connection.prepareStatement(sqlString);
            ps.setInt(1, sfdcServerId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                sInfo.setServerId(rs.getInt("server_id"));
                sInfo.setName(rs.getString("name"));
                sInfo.setProgramId(rs.getInt("program_id"));
                sInfo.setPortalserverId(rs.getInt("portal_server_id"));
                sInfo.setIp(rs.getString("ip"));
                sInfo.setPort(rs.getString("port"));
                sInfo.setContextpath(rs.getString("context_path"));
                sInfo.setStatus(rs.getString("status"));
                sInfo.setCustomer(rs.getString("customer"));
                sInfo.setDescription(rs.getString("description"));
                sInfo.setServerservice(rs.getString("serverservice"));
                sInfo.setCommunityId(rs.getInt("community_id"));
                sInfo.setDirectconnection(rs.getString("direct_connection"));
                sInfo.setNetuserid(rs.getString("net_user_id"));
                sInfo.setSfdctimezone(rs.getString("sfdc_timezone"));
                sInfo.setSfdcIp(rs.getString("sfdc_ip"));
                sInfo.setAgileurlvirtualpath(rs.getString("agileurlvirtualpath"));
                sInfo.setAgileurlobjecttype(rs.getString("agileurlobjecttype"));
                sInfo.setAgileurlrevision(rs.getString("agileurlrevision"));
                sInfo.setAgileurlfolder(rs.getString("agileurlfolder"));
                sInfo.setAgileurlfiles(rs.getString("agileurlfiles"));
                sInfo.setAgileurllink(rs.getString("agileurllink"));
            }
            rs.close();
            ps.close();
            connection.close();
        } catch (SQLException e) {
           // logger.error(e.getMessage());
            e.printStackTrace();
        } finally {
            return sInfo;
        }
    }

    /**
     * getServerInfoByProgramId
     *
     * @param portalHost,
     *            programId
     * @return serverInfo
     */
    @SuppressWarnings("finally")
    @Override
    public ServerInfo getConduitServerInfo(String portalHost, int sitecommunityId, int programId, String contextPath) {
        ServerInfo sInfo = new ServerInfo();
        String portalHostUrl = portalHost + ":5432/mesportal";
        String sqlString = "select * from mes_server_info where program_id=? and community_id=? and context_path=?";
        Connection connection = getConnection(portalHostUrl);
        try {
            PreparedStatement ps = connection.prepareStatement(sqlString);
            ps.setInt(1, programId);
            ps.setInt(2, sitecommunityId);
            ps.setString(3, contextPath);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                sInfo.setServerId(rs.getInt("server_id"));
                sInfo.setName(rs.getString("name"));
                sInfo.setProgramId(rs.getInt("program_id"));
                sInfo.setPortalserverId(rs.getInt("portal_server_id"));
                sInfo.setIp(rs.getString("ip"));
                sInfo.setPort(rs.getString("port"));
                sInfo.setContextpath(rs.getString("context_path"));
                sInfo.setStatus(rs.getString("status"));
                sInfo.setCustomer(rs.getString("customer"));
                sInfo.setDescription(rs.getString("description"));
                sInfo.setServerservice(rs.getString("serverservice"));
                sInfo.setCommunityId(rs.getInt("community_id"));
                sInfo.setDirectconnection(rs.getString("direct_connection"));
                sInfo.setNetuserid(rs.getString("net_user_id"));
                sInfo.setSfdctimezone(rs.getString("sfdc_timezone"));
                sInfo.setSfdcIp(rs.getString("sfdc_ip"));
               /* sInfo.setAgileurlvirtualpath(rs.getString("agileurlvirtualpath"));
                sInfo.setAgileurlobjecttype(rs.getString("agileurlobjecttype"));
                sInfo.setAgileurlrevision(rs.getString("agileurlrevision"));
                sInfo.setAgileurlfolder(rs.getString("agileurlfolder"));
                sInfo.setAgileurlfiles(rs.getString("agileurlfiles"));
                sInfo.setAgileurllink(rs.getString("agileurllink"));*/
            }
            rs.close();
            ps.close();
            connection.close();
        } catch (SQLException e) {
            Log.i("SQLException",e.getMessage());
            e.printStackTrace();
        } finally {
            return sInfo;
        }
    }

    /**
         * queryProjectsOfPlant : get all the sites of a plant
         *
         * @param :
         *            String portalHost, String plantCode
         * @reture : List<site>
         */
        @SuppressWarnings("finally")
        @Override
        public List<Site> queryProjectsOfPlant(String portalHost, String plantCode) {
            List<Site> resuList = new ArrayList<Site>();
            List<String> siteNameList = new ArrayList<String>();
            String portalHostUrl = portalHost + ":5432/lportal";
            String sqlString = "select name from organization_ where parentorganizationid="
                    + "(select organizationid from organization_ where name=?)";
            String siteSql = "select * from group_ where name=?";
            Connection conn = getConnection(portalHostUrl);
            PreparedStatement ps = null;
            ResultSet rs = null;
            try {
                ps = conn.prepareStatement(sqlString);
                ps.setString(1, plantCode);
                rs = ps.executeQuery();
                while (rs.next()) {
                    siteNameList.add(rs.getString(1));
                }
                for (int i = 0; i < siteNameList.size(); i++) {
                    ps = conn.prepareStatement(siteSql);
                    ps.setString(1, siteNameList.get(i));
                    rs = ps.executeQuery();
                    while (rs.next()) {
                        Site site = new Site();
                        site.setGroupid(rs.getInt("groupid"));
                       // site.setCompanyid(rs.getInt("companyid"));
                        site.setName(rs.getString("name"));
                        site.toString();
                        resuList.add(site);
                    }
                }
                rs.close();
                ps.close();
                conn.close();
            } catch (SQLException e) {

                e.printStackTrace();
            } finally {
                return resuList;
            }
        }
    public List<SFDCConf> getStationId(String portalHost, int serverId) {
        List<SFDCConf> resultList = new ArrayList<SFDCConf>();
        String portalHostUrl = portalHost + ":5432/mesportal";
        String sqlString = "select * from emanual_sfdc_config where server_id=? order by device_id ASC";
        Connection conn = getConnection(portalHostUrl);
        try {
            PreparedStatement ps = conn.prepareStatement(sqlString);
            ps.setInt(1, serverId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                SFDCConf sfdcConf = new SFDCConf();
                sfdcConf.setId(rs.getInt("id"));
                sfdcConf.setSfdcport(rs.getString("sfdc_port"));
                sfdcConf.setDeviceid(rs.getString("device_id"));
                sfdcConf.setDescription(rs.getString("description"));
                sfdcConf.setClientname(rs.getString("client_name"));
                sfdcConf.setPlant(rs.getString("plant"));
                sfdcConf.setActive(rs.getString("active"));
                sfdcConf.setDevicetype(rs.getString("device_type"));
                sfdcConf.setServerid(rs.getInt("server_id"));
                sfdcConf.setDevicecode(rs.getString("device_code"));
                resultList.add(sfdcConf);
            }
            rs.close();
            ps.close();
            conn.close();
        } catch (SQLException e) {
           Log.i("SQLException",e.getMessage());
            e.printStackTrace();
        }

        return resultList;
    }


    /**
     * getProgramId
     *
     * @return int programId
     * @param programName
     */
    public int getProgramId(String portalHost, String programName) {
        String portalHostUrl = portalHost + ":5432/mesportal";
        String sqlString = "select program_id from mes_program_info where name=?; ";
        Connection conn = getConnection(portalHostUrl);
        int programId = 0;
        try {
            PreparedStatement ps = conn.prepareStatement(sqlString);
            ps.setString(1, programName);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                programId = rs.getInt(1);
            }
            rs.close();
            ps.close();
            conn.close();
        } catch (SQLException e) {
            Log.i("SQLException", e.getMessage());
            e.printStackTrace();
        }
        return programId;
    }
    /**
     * getServerInfo : get SFDC server info of a site
     *
     * @param :
     *            int sitecommunityId
     * @reture : serverInfo
     */
    @SuppressWarnings("finally")
    @Override
    public List<ServerInfo> getServerInfo(String portalHost, int sitecommunityId, int programId) {
        List<ServerInfo> resultList = new ArrayList<ServerInfo>();
        String portalHostUrl = portalHost + ":5432/mesportal";
        String sqlString = "select * from mes_server_info where program_id=? and community_id=? order by server_id ASC";
        Connection connection = getConnection(portalHostUrl);
        try {
            PreparedStatement ps = connection.prepareStatement(sqlString);
            ps.setInt(1, programId);
            ps.setInt(2, sitecommunityId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ServerInfo sInfo = new ServerInfo();
                sInfo.setServerId(rs.getInt("server_id"));
                sInfo.setName(rs.getString("name"));
                sInfo.setProgramId(rs.getInt("program_id"));
                sInfo.setPortalserverId(rs.getInt("portal_server_id"));
                sInfo.setIp(rs.getString("ip"));
                sInfo.setPort(rs.getString("port"));
                sInfo.setContextpath(rs.getString("context_path"));
                sInfo.setStatus(rs.getString("status"));
                sInfo.setCustomer(rs.getString("customer"));
                sInfo.setDescription(rs.getString("description"));
                sInfo.setServerservice(rs.getString("serverservice"));
                sInfo.setCommunityId(rs.getInt("community_id"));
                sInfo.setDirectconnection(rs.getString("direct_connection"));
                sInfo.setNetuserid(rs.getString("net_user_id"));
                sInfo.setSfdctimezone(rs.getString("sfdc_timezone"));
                sInfo.setSfdcIp(rs.getString("sfdc_ip"));
                sInfo.setAgileurlvirtualpath(rs.getString("agileurlvirtualpath"));
                sInfo.setAgileurlobjecttype(rs.getString("agileurlobjecttype"));
                sInfo.setAgileurlrevision(rs.getString("agileurlrevision"));
                sInfo.setAgileurlfolder(rs.getString("agileurlfolder"));
                sInfo.setAgileurlfiles(rs.getString("agileurlfiles"));
                sInfo.setAgileurllink(rs.getString("agileurllink"));
                resultList.add(sInfo);
            }
            rs.close();
            ps.close();
            connection.close();
        } catch (SQLException e) {
            Log.i("SQLException",e.getMessage());
            e.printStackTrace();
        } finally {
            return resultList;
        }
    }

}





